/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package xml.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import xml.entity.Account;
import xml.entity.Employer;
import xml.util.DBConnection;

/**
 *
 * @author Kyros
 */
public class EmployerDAO {

    DBConnection conInfo;

    public int getNextEmployerId() throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();
        ResultSet rs = null;
        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            String strQuery = "SELECT employerId FROM employer";

            stm = conn.createStatement();
            rs = stm.executeQuery(strQuery);

            int result = 0;
            int i = 0;
            while (rs.next()) {
                result = Integer.parseInt(rs.getString("employerId"));
                i = i++;
            }
            
//            System.out.println("asdsad" + result);
            stm.close();
            conn.close();
            
            return result+1;
            
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public void insertEmployer(Employer employer) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            StringBuilder strBuilder = new StringBuilder();

            strBuilder.append("INSERT INTO Employer (employerId, account_username, companyName, "
                    + "companyDesc, companyAddress, city, website, fullname, currentPos, phone, email, address)"
                    + " VALUES ('").append(employer.getEmployerId()).
                    append("', '").append(employer.getAccountUsername()).
                    append("', '").append(employer.getCompanyName()).
                    append("', '").append(employer.getCompanyDesc()).
                    append("', '").append(employer.getCompanyAddress()).
                    append("', '").append(employer.getCity()).append("', '").
                    append(employer.getWebsite()).append("', '").
                    append(employer.getFullname()).append("', '").
                    append(employer.getCurrentPos()).append("', '").
                    append(employer.getPhone()).append("', '").
                    append(employer.getEmail()).append("', '").
                    append(employer.getAddress()).append("')");

            stm = conn.createStatement();
            stm.executeUpdate(strBuilder.toString());

            stm.close();
            conn.close();
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public void updateEmployer(Employer employer, String username) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            StringBuilder strBuilder = new StringBuilder();

            strBuilder.append("UPDATE Employer SET companyName = '").append(employer.getCompanyName()).append("', ");
            strBuilder.append("companyDesc = '").append(employer.getCompanyDesc()).append("', ");
            strBuilder.append("companyAddress = '").append(employer.getCompanyAddress()).append("', ");
            strBuilder.append("city = '").append(employer.getCity()).append("', ");
            strBuilder.append("website = '").append(employer.getWebsite()).append("', ");
            strBuilder.append("fullname = '").append(employer.getFullname()).append("', ");
            strBuilder.append("currentPos = '").append(employer.getCurrentPos()).append("', ");
            strBuilder.append("phone = '").append(employer.getPhone()).append("', ");
            strBuilder.append("email = '").append(employer.getEmail()).append("', ");
            strBuilder.append("address = '").append(employer.getAddress()).append("' ");
            strBuilder.append(" WHERE account_username = '").append(username).append("'");

       //     System.out.println("update employer query:" + strBuilder);
            stm = conn.createStatement();
            stm.executeUpdate(strBuilder.toString());

            stm.close();
            conn.close();
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }
    
    public int getEmployerIdByUsername(String username) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();
        ResultSet rs = null;
        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            String strQuery = "SELECT employerId FROM employer WHERE account_username = '" + username + "'";

            stm = conn.createStatement();
            rs = stm.executeQuery(strQuery);

            int result = 0;
            while (rs.next()) {
                result = rs.getInt("employerId");
            }
            
//            System.out.println("asdsad" + result);
            stm.close();
            conn.close();
            
            return result;
            
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

        public String getUsernameByEmployerId(int employerId) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();
        ResultSet rs = null;
        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            String strQuery = "SELECT account_username FROM employer WHERE employerId = '" + employerId + "'";

            stm = conn.createStatement();
            rs = stm.executeQuery(strQuery);

            String result = "";
            while (rs.next()) {
                result = rs.getString("account_username");
            }
            
//            System.out.println("asdsad" + result);
            stm.close();
            conn.close();
            
            return result;
            
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }
        
    public Employer getCurrentEmployer(String username) throws SQLException, Exception {
        Employer emp = new Employer();
        Connection conn = null;
        Statement stm = null;
        ResultSet rs = null;
        conInfo = new DBConnection();

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            StringBuilder strBuilder = new StringBuilder();
            strBuilder.append("SELECT * FROM employer");
            strBuilder.append(" WHERE account_username = '").append(username).append("'");
            stm = conn.createStatement();
            rs = stm.executeQuery(strBuilder.toString());
            while (rs.next()) {
                emp.setCompanyName(rs.getString("companyName"));
                emp.setCompanyDesc(rs.getString("companyDesc"));
                emp.setCompanyAddress(rs.getString("companyAddress"));
                emp.setCity(rs.getString("city"));
                emp.setWebsite(rs.getString("website"));
                emp.setFullname(rs.getString("fullname"));
                emp.setCurrentPos(rs.getString("currentPos"));
                emp.setPhone(rs.getString("phone"));
                emp.setEmail(rs.getString("email"));
                emp.setAddress(rs.getString("address"));
            }
            return emp ;

        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }
}
